/*
 *	This file is part of Lounge-Cast.
 *
 *  Author:  	Markus Moormann
 *	E-mail:  	mmoorman@uni-osnabrueck.de
 *	Copyright:	University of Osnabrueck (Zentrum Virtuos)
 * 	Website:	www.uni-osnabrueck.de
 *	Date:		16.02.2011
 *
 *	Lounge-Cast is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *	Lounge-Cast is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with Lounge-Cast.  If not, see <http://www.gnu.org/licenses/>.
 */
package webservice.functions;

import java.awt.image.BufferedImage;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.imageio.ImageIO;
import javax.ws.rs.DefaultValue;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.ProduceMime;
import javax.ws.rs.QueryParam;
import javax.ws.rs.WebApplicationException;
import javax.ws.rs.core.Response;

import org.apache.log4j.Logger;

import com.sun.syndication.feed.synd.SyndEntry;
import com.sun.syndication.feed.synd.SyndFeed;
import com.sun.syndication.io.FeedException;
import com.sun.syndication.io.SyndFeedInput;
import com.sun.syndication.io.XmlReader;
import com.twmacinta.util.MD5;

import webservice.dbConnection.ConnectionHelper;
import webservice.helper.HTMLUtil;
import webservice.helper.HelperClass;
import webservice.vo.FeedVO;

/**
 * class for automatic feed update
 *
 * @author Markus Moormann
 * @version 04.01.2011
 *
 */
@Path("/updateFeeds")
@ProduceMime("application/xml")
public class UpdateFeedsResource 
{
	
	/**
	 * a logger to log errors, debug information, etc.
	 */
	private static Logger LOG = Logger.getLogger(UpdateFeedsResource.class);
	
	/**
	 * a hashMap mapping the feedId to a Long representing a UnixTimestamp
	 */
	private static HashMap<Integer, Long> feedUpdates = new HashMap<Integer, Long>();
	
	@GET
	public String updateFeed(@QueryParam("apiKey") @DefaultValue("") String apiKey,
							 @QueryParam("feed_id")  @DefaultValue("") String feed_id) 
	{
		//is apiKey correct?
		if(!apiKey.equals(HelperClass.getProperty("apiKey"))) {
			throw new WebApplicationException(Response.Status.FORBIDDEN);
		}
		LOG.debug("UPDATING FEEDS");
		updateFeeds(feed_id);
		return "<result>true</result>";
	}
	
	/**
	 * update the feeds by selecting all feeds whose last update is older than one day
	 */
	private void updateFeeds(String feed_id) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectFeeds = "SELECT " +
									"feed_id," +
									"url," +
									"title," +
									"trust_level," +
									"playlist_id," +
									"feed_item_valid_time," +
									"auto_save_to_playlist," +
									"background_color," +
									"add_to_newsticker," +
									"feed_item_duration," +
									"feed_item_background_image," +
									"feed_item_margin_bottom, " +
									"feed_item_margin_left, " +
									"feed_item_margin_top, " +
									"feed_item_margin_right " +
								 "FROM " +
								 	"feed " +
								 (feed_id.equals("") ? 
									 "WHERE " +
									 	"UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(feed_last_update) > 60*60" :
									 "WHERE " +
									 	"feed_id = " + feed_id);
			ResultSet rs = statement.executeQuery(selectFeeds);
			while(rs.next()) {
				FeedVO feed = new FeedVO();
				feed.feed_id = rs.getInt("feed_id");
				feed.title = rs.getString("title");
				feed.url = rs.getString("url");
				feed.trust_level = rs.getInt("trust_level");
				feed.playlist_id = rs.getInt("playlist_id");
				feed.feed_item_valid_time = rs.getLong("feed_item_valid_time");
				feed.auto_save_to_playlist = rs.getBoolean("auto_save_to_playlist");
				feed.background_color = rs.getInt("background_color");
				feed.add_to_newsticker = rs.getBoolean("add_to_newsticker");
				feed.feed_item_duration = rs.getInt("feed_item_duration");
				feed.feed_item_background_image = rs.getString("feed_item_background_image");
				feed.feed_item_margin_bottom = rs.getInt("feed_item_margin_bottom");
				feed.feed_item_margin_left = rs.getInt("feed_item_margin_left");
				feed.feed_item_margin_top = rs.getInt("feed_item_margin_top");
				feed.feed_item_margin_right = rs.getInt("feed_item_margin_right");
				updateFeedItem(feed, !feed_id.equals(""));
			}
		} catch(SQLException e) {
			LOG.error(e);
			throw new WebApplicationException(Response.Status.INTERNAL_SERVER_ERROR);
		} finally {
			ConnectionHelper.close(statement);
			ConnectionHelper.close(connection);
		}
	}
	
	/**
	 * update the feed in database. Items that are not any longer in the feed, are 
	 * deleted. 
	 *  
	 * @param feed the feed to update
	 */
	private void updateFeedItem(FeedVO feed, boolean forceUpdate) {
		Connection connection = null;
		Statement statement = null;
		try {
			LOG.debug("updating feeed " + feed.feed_id);
			String selectFeedItem = "SELECT " +
										"feed_item_uri " +
									"FROM " +
										"feed_item " +
									"WHERE " +
										"feed_id = " + feed.feed_id + " AND " +
										"feed_item_uri = ? ";
			
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			PreparedStatement ps = connection.prepareStatement(selectFeedItem);
			
			List<String> itemURIs = new LinkedList<String>();
			List<SyndEntry> addableEntries = new LinkedList<SyndEntry>();
			
			Long lastUpdate = feedUpdates.get(feed.feed_id);
			if(lastUpdate == null || (System.currentTimeMillis() - lastUpdate) > 60 * 60 * 1000 || forceUpdate) {
				List<SyndEntry> entries = getEntries(feed.url);
				ResultSet rs;
				for(SyndEntry entryTmp : entries) {
					ps.setString(1, entryTmp.getUri());
					rs = ps.executeQuery();
					if(!rs.next())
					{
						addableEntries.add(entryTmp);
					}
					itemURIs.add("'" + entryTmp.getUri() + "'");
			}
				
				String insertItems = "INSERT INTO " +
										"feed_item " +
											"(" +
												"feed_item_uri," +
												"feed_id," +
												"feed_item_title," +
												"feed_item_link," +
												"feed_item_description," +
												"feed_item_pubDate," +
												"feed_item_status," +
												"feed_item_media_id" +
											")VALUES" +
											"(?,?,?,?,?,?,?,?)";
				String updateFeed = "UPDATE " +
										"feed " +
									"SET " +
										"feed_last_update = NOW() " +
									"WHERE " +
										"feed_id = " + feed.feed_id;
				String insertMediaInPlaylist = 	"INSERT INTO " +
													"media_playlist_items" +
														"(" +
															"playlist_id," +
															"media_id," +
															"position" +
														") VALUES" +
														"(" +
															"?," +
															"?," +
															"?" +
														")";
				String deleteMedia = "DELETE FROM " +
										"media " +
									 "WHERE " +
									 	"media_id IN " +
									 	"(" +
									 		"SELECT " +
									 			"feed_item_media_id " +
									 		"FROM " +
									 			"feed_item " +
									 		"WHERE " +
										 		"feed_item_uri NOT IN (" + 
										 			HelperClass.implode(itemURIs.toArray(new String[itemURIs.size()]), ",") + ") AND " +
										 		"feed_id = " + feed.feed_id + 
									 	")";
				String deleteItems = "DELETE FROM " +
										"feed_item " +
									 "WHERE " +
									 	"feed_item_uri NOT IN (" + 
									 		HelperClass.implode(itemURIs.toArray(new String[itemURIs.size()]), ",") + ") AND " +
									 	"feed_id = " + feed.feed_id;
				//only if there are items to insert
				if(addableEntries.size() > 0) {
					PreparedStatement prep = connection.prepareStatement(insertItems);
					PreparedStatement mediaPlaylists = connection.prepareStatement(insertMediaInPlaylist);
					int nr = 0;
					
					for(SyndEntry entry : addableEntries) {
						Date date = entry.getPublishedDate();
						date = (date == null ? entry.getUpdatedDate() : date);
						date = (date == null ? new Date() : date);
						MD5 media_id = new MD5();
						media_id.Update(entry.getUri());
						//feed items should be added to playlist automatically
						//if(feed.auto_save_to_playlist) {
							
						//}
						
						prep.setString(1, entry.getUri());
						prep.setInt(2, feed.feed_id);
						prep.setString(3, entry.getTitle());
						prep.setString(4, entry.getLink());
						prep.setString(5, (entry.getDescription() == null ? "" : entry.getDescription().getValue()));
						prep.setString(6, HelperClass.format(date));
						prep.setString(7, (feed.add_to_newsticker ? "include" : "exclude"));
						prep.setString(8, media_id.asHex());
						prep.execute();
						
						autoSave(entry, feed);
						mediaPlaylists.setInt(1, feed.playlist_id);
						mediaPlaylists.setString(2, media_id.asHex());
						mediaPlaylists.setInt(3, nr);
						mediaPlaylists.execute();
					}
					String updatePlayer = 	"UPDATE " +
												"player " +
											"SET " +
												"last_update = NOW() " +
											"WHERE " +
												"player_id IN " +
												"(" +
													"SELECT " +
														"player_id " +
													"FROM " +
														"playlist_player " +
													"WHERE " +
														"playlist_id = " + feed.playlist_id +
												")";
					statement.execute(updatePlayer);
					statement.executeUpdate(deleteItems);
					statement.executeUpdate(deleteMedia);
				}
				statement.executeUpdate(updateFeed);
				statement.execute("SET @count = 0");
				String updatePosition = "UPDATE " +
											"media_playlist_items " +
										"SET " +
											"position = (SELECT @count := @count + 1)" +
										"WHERE " +
											"playlist_id = " + feed.playlist_id + " " +
										"ORDER BY " +
											"playlist_item_id ASC";
				statement.executeUpdate(updatePosition);
				feedUpdates.put(feed.feed_id, System.currentTimeMillis());
			}
			
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			throw new WebApplicationException(Response.Status.INTERNAL_SERVER_ERROR);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
	}
	
	/**
	 * function for autosaving FeedItems to a playlist
	 * therefore some things have to be done:
	 * <ul>
	 * 	<li>get the image from the feed item and remove it from item</li>
	 * 	<li>remove all HTML-Tags from the item apart from &lt;b&gt; and &lt;u&gt;</li>
	 * 	<li>insert feed item as media into database</li>
	 *  <li>link media to correct playlist</li>
	 * </ul>
	 * 
	 * @param entry the feed item
	 * @param feed the feed the item belongs to
	 * @return the media_id of the created MediaVO
	 */
	private String autoSave(SyndEntry entry, FeedVO feed) {
		String imageURL = "";
		String image_media_id = null;
		MD5 media_id = new MD5();
		
		String srcRegex = "(?<=src=['|\"])[^'|\"]*?(?=['|\"])";
		String hrefRegex = "(?<=href=['|\"])[^'|\"]*?(?=['|\"])";
		
		Matcher srcMatch;
		Matcher hrefMatch;
		
		//find the image URL if there is one
		srcMatch = Pattern.compile(srcRegex, Pattern.CASE_INSENSITIVE).matcher(entry.getDescription() == null ? "" : entry.getDescription().getValue());
		hrefMatch = Pattern.compile(hrefRegex, Pattern.CASE_INSENSITIVE).matcher(entry.getDescription() == null ? "" : entry.getDescription().getValue());
		
		if(srcMatch.find())
		{
			imageURL = srcMatch.group();
			if(hrefMatch.find())
			{
				imageURL = hrefMatch.group();
			}
			image_media_id = entry.getUri() + "image";
			if(imageURL.equals(""))
			{
				image_media_id = null;
			}
		} 
		
		String text = HTMLUtil.stripHtmlTags(HTMLUtil.removeHref(HTMLUtil.removeTitle(entry.getDescription() == null ? "" : entry.getDescription().getValue())), "").trim().replaceAll("[\r\n]+", "");
		String formated_text = HTMLUtil.stripHtmlTags(HTMLUtil.removeHref(HTMLUtil.removeTitle(entry.getDescription() == null ? "" : entry.getDescription().getValue())), "b,u").trim().replaceAll("[\r\n]+", "");
		String imageQuery = "INSERT INTO " +
								"`image` " +
									"(" +
										"`media_id`," 		+
										"`url`," 		+
										"`image_source`," +
										"`in_composition`," +
										"`width`," +
										"`height`," +
										"`aspect_ratio` "	+
									") VALUES " +
									"(" +
										"MD5(?)," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?" +
									")";
		String textQuery = "INSERT INTO " +
								"`text` " +
									"(" +
										"`media_id`," 		+
										"`text`," 			+
										"`formated_text`,"  +
										"`background_color`,"  +
										"`skin`," 			+
										"`image`," 			+
										"`image_orientation`," +
										"`background_image`," +
										"`margin_bottom`," +
										"`margin_left`," +
										"`margin_top`," +
										"`margin_right`" +
									") VALUES " +
									"(" +
										"MD5(?)," +
										"?," +
										"?," +
										"?," +
										"?," +
										"MD5(?)," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?" +
									")";
		String mediaQuery = "INSERT INTO " +
								"`media` " +
									"(" +
										"`media_id`," 	+
										"`type`," 		+
										"`title`," 		+
										"`start_date`," +
										"`valid_date`," +
										"`newsticker`," +
										"`duration`," 	+
										"`reference`," 	+
										"`added_by`," 	+
										"`approved`," 	+
										"`converted`," +
										"`feed_item_uri`" +
										"" 	+
									") VALUES " +
									"(" +
										"MD5(?)," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?," +
										"?"  +
									")";
		PreparedStatement ps;
		PreparedStatement textStatement;
		PreparedStatement imageStatement;
		Connection connection = null;
		try 
		{
			connection = ConnectionHelper.getConnection();
			ps = connection.prepareStatement(mediaQuery);
			media_id.Update(entry.getUri());
			if(image_media_id != null)
			{
				
				int width = 0, height = 0;
				double aspect_ratio = 0;
				BufferedImage im = getImage(imageURL);
				if(im != null) {
					width = im.getWidth();
					height = im.getHeight();
					aspect_ratio = (double)im.getWidth() / (double)im.getHeight();
				}
				
				//insert media for image
				ps.setString(1, image_media_id);
				ps.setString(2, "image");
				ps.setString(3, entry.getTitle());
				ps.setString(4, HelperClass.format(new Date()));
				ps.setString(5, HelperClass.format(new Date(new Date().getTime() + feed.feed_item_valid_time * 1000)));
				ps.setString(6, Boolean.toString(false));
				ps.setLong(7, feed.feed_item_duration);
				ps.setString(8, feed.url);
				ps.setInt(9, 0);
				ps.setString(10, Boolean.toString(feed.trust_level == 1));
				ps.setString(11, Boolean.toString(true));
				ps.setString(12, entry.getUri());
				ps.execute();
				
				
				if(!imageURL.contains(HelperClass.getProperty("webserviceURL")) && !imageURL.equals("")) {
					imageURL = insertRemoteFile(imageURL, false);
					LOG.debug(imageURL);
				}
				imageStatement = connection.prepareStatement(imageQuery);
				imageStatement.setString(1, image_media_id);
				imageStatement.setString(2, imageURL);
				imageStatement.setString(3, feed.title);
				imageStatement.setString(4, Boolean.toString(true));
				imageStatement.setInt(5, width);
				imageStatement.setInt(6, height);
				imageStatement.setDouble(7, aspect_ratio);
				imageStatement.execute();
			}
			
			//insert media for text
			ps.setString(1, entry.getUri());
			ps.setString(2, "text");
			ps.setString(3, entry.getTitle());
			ps.setString(4, HelperClass.format(new Date()));
			ps.setString(5, HelperClass.format(new Date(new Date().getTime() + feed.feed_item_valid_time * 1000)));
			ps.setString(6, Boolean.toString(false));
			ps.setLong(7, 20);
			ps.setString(8, feed.url);
			ps.setInt(9, 0);
			ps.setString(10, Boolean.toString(true));
			ps.setString(11, Boolean.toString(true));
			ps.setString(12, entry.getUri());
			ps.execute();
			
			textStatement = connection.prepareStatement(textQuery);
			textStatement.setString(1, entry.getUri());
			textStatement.setString(2, text);
			textStatement.setString(3, formated_text);
			textStatement.setInt(4, feed.background_color);
			textStatement.setString(5, "");
			textStatement.setString(6, image_media_id);
			textStatement.setString(7, "left");
			textStatement.setString(8, feed.feed_item_background_image);
			textStatement.setInt(9 , feed.feed_item_margin_bottom);
			textStatement.setInt(10, feed.feed_item_margin_left);
			textStatement.setInt(11, feed.feed_item_margin_top);
			textStatement.setInt(12, feed.feed_item_margin_right);
			textStatement.execute();
			
		} catch (SQLException e) {
			LOG.error(e);
			throw new WebApplicationException(Response.Status.INTERNAL_SERVER_ERROR);
		} finally
		{
			ConnectionHelper.close(connection);
		}

		media_id = new MD5();
		media_id.Update(entry.getUri());
		
		return media_id.asHex();
	}
	
	/**
	 * insert the remote URL into database
	 * 
	 * @param remoteURL the URL to save
	 * @param slide true if remoteURL points to converted slides folder, false els
	 * 
	 * @return the new URL 
	 */
	private String insertRemoteFile(String remoteURL, boolean slide) {
		String url = HelperClass.getProperty("webserviceURL") + "/uploadedFiles/remote" + (slide ? "Slide" : "") + "/";
		MD5 remote_file_id = new MD5();
		Connection connection = null;
		Statement statement = null;
		PreparedStatement ps = null;
		try {
			connection = ConnectionHelper.getConnection();
			
			remote_file_id.Update(remoteURL + new Date().getTime());
			
			url += remote_file_id.asHex();
			
			String insertRemote = 	"INSERT INTO " +
										"remote_files " +
											"(" +
												"remote_file_id," +
												"remote_url" +
											") VALUES" +
											"(" +
												"?," +
												"?" +
											")";
			ps = connection.prepareStatement(insertRemote);
			ps.setString(1, remote_file_id.asHex());
			ps.setString(2, remoteURL);
			ps.execute();
		} catch (SQLException e) {
			LOG.error("SQLException when inserting remoteURL" + e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return url;
	}
	
	/**
	 * get a <code>BufferedImage</code> by given URL
	 * 
	 * @param url the URL of the file
	 * @return the a <code>BufferedImage</code> containing the image
	 */
	private BufferedImage getImage(String url) {
		
		BufferedImage im = null;
		Connection connection = null;
		Statement statement = null;
		try {
			
//			connection = ConnectionHelper.getConnection();
//			statement = connection.createStatement();
//			
//			String selectImageFromUploaded = 	"SELECT " +
//													"path " +
//												"FROM " +
//													"uploaded_files " +
//												"WHERE " +
//													"url = '" + url + "'";
//			ResultSet rs = statement.executeQuery(selectImageFromUploaded);
//			
//			if (rs.next()) {
//				im = ImageIO.read(new File(rs.getString("path")));
//			}
			if (im == null) {
				im = ImageIO.read(new URL(url));
			}
			
			
		}catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return im;
	}
	
	/**
	 * gets all entries for a certain feed
	 * 
	 * @param url the URL of a feed
	 * @return a <code>List<SyndEntry></code> containing the entries of the feed
	 */
	private List<SyndEntry> getEntries(String url) {
		List<SyndEntry> result = new LinkedList<SyndEntry>();
		URLConnection feedURL;
		try {
			feedURL = new URL(url).openConnection();
			SyndFeedInput input = new SyndFeedInput();
			SyndFeed feed = input.build(new XmlReader(feedURL));
			List<?> tmp = feed.getEntries();
			for(int i = 0; i < tmp.size(); i++) {
				result.add((SyndEntry) tmp.get(i));
			}
		} catch (MalformedURLException e) {
			LOG.error(e);
			throw new WebApplicationException(Response.Status.INTERNAL_SERVER_ERROR);
		} catch (IOException e) {
			LOG.error(e);
			throw new WebApplicationException(Response.Status.INTERNAL_SERVER_ERROR);
		} catch (IllegalArgumentException e) {
			LOG.error(e);
			throw new WebApplicationException(Response.Status.INTERNAL_SERVER_ERROR);
		} catch (FeedException e) {
			LOG.error(e);
			throw new WebApplicationException(Response.Status.INTERNAL_SERVER_ERROR);
		}
		return result;
	}

}
